using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace PluginHandler.Database
{
    class DatabaseHandler : IDatabaseHandler
    {
        public event ScriptProcessedEventHandler ScriptProcessed;

        private DbConnectionStringBuilder _configuration;
        private DatabaseStructure _databaseStructure;
        private IDbConnection _databaseConnection;

        internal DatabaseHandler()
        { 
        }

        #region IDatabaseHandler Member

        public void LoadDatabase(DbConnectionStringBuilder configuration)
        {
            _configuration = configuration;
            ReloadDatabase();
        }

        public void ReloadDatabase()
        {
            if (_configuration != null)
            {
                //***********************************************
                //***********************************************
                //TODO: Open the connection and set all variables
                //e.g.:
                //_databaseConnection = new SqlConnection(_configuration.ConnectionString);

                _databaseConnection = new System.Data.SqlClient.SqlConnection(_configuration.ConnectionString);
                
                //***********************************************
                //***********************************************

                _databaseConnection.Open();
                _databaseStructure = new DatabaseStructure(this);
            }
            else
            {
                throw new Exception("Database is not loaded!");
            }
        }

        public void UnloadDatabase()
        {
            if (_configuration != null)
            {
                //closes the connection, if it is not closed
                if(_databaseConnection.State != ConnectionState.Closed)
                    _databaseConnection.Close();

                //set all variables to null
                _configuration = null;
                _databaseStructure = null;
                _databaseConnection = null;
            }
        }

        public IDatabaseStructure DatabaseStructure
        {
            get 
            {
                return _databaseStructure;
            }
        }

        public void ExecuteNonQuery(string commandText)
        {
            //***********************************************
            //***********************************************
            //TODO: Executes a query, which has no resultset
            //e.g.:
            //SqlCommand command = ((SqlConnection)_databaseConnection).CreateCommand();
            //command.CommandText = commandText;
            //command.ExecuteNonQuery();
            
            System.Data.SqlClient.SqlCommand command = ((System.Data.SqlClient.SqlConnection)_databaseConnection).CreateCommand();
            command.CommandText = commandText;
            command.ExecuteNonQuery();

            //***********************************************
            //***********************************************
        }

        public IDataReader ExecuteReader(string commandText)
        {
            //***********************************************
            //***********************************************
            //TODO: Executes a query, which returns a full resultset
            //e.g.:
            //SqlCommand command = ((SqlConnection)_databaseConnection).CreateCommand();
            //command.CommandText = commandText;
            //return command.ExecuteReader();

            System.Data.SqlClient.SqlCommand command = ((System.Data.SqlClient.SqlConnection)_databaseConnection).CreateCommand();
            command.CommandText = commandText;
            return command.ExecuteReader();

            //***********************************************
            //***********************************************
        }

        public object ExecuteScalar(string commandText)
        {
            //***********************************************
            //***********************************************
            //TODO: Executes a query, which returns the first cell in the first row of te resultset
            //e.g.:
            //SqlCommand command = ((SqlConnection)_databaseConnection).CreateCommand();
            //command.CommandText = commandText;
            //return command.ExecuteScalar();

            System.Data.SqlClient.SqlCommand command = ((System.Data.SqlClient.SqlConnection)_databaseConnection).CreateCommand();
            command.CommandText = commandText;
            return command.ExecuteScalar();

            //***********************************************
            //***********************************************
        }

        public void ExecuteDatabaseScript(string scriptSource)
        {
            //***********************************************
            //***********************************************
            //TODO: Prepares the whole script source and executes each query separately to fire the ScriptProcessed event
            //e.g.:
            //string[] statements = scriptSource.Split(
            //    new string[] { "\r\nGO\r\n" }, 
            //    StringSplitOptions.RemoveEmptyEntries);

            //for (int i = 0; i < statements.Length; i++)
            //{
            //    ExecuteNonQuery(statements[i]);

            //    if (ScriptProcessed != null)
            //    {
            //        ScriptProcessed(
            //            this, 
            //            new ScriptProcessedEventArgs(
            //                statements[i], 
            //                i, 
            //                statements.Length));
            //    }
            //}

            //***********************************************
            //***********************************************
        }

        public IDbConnection DatabaseConnection
        {
            get 
            {
                return _databaseConnection;
            }
        }

        public DbConnectionStringBuilder ConnectionStringDetails
        {
            get 
            {
                return _configuration;
            }
        }
        #endregion
    }
}
